Java书签 #解锁MyBatis的4种批量插入方式及ID返回姿势 |
您所在的位置：网站首页 › mybatis-plus insert方法返回id › Java书签 #解锁MyBatis的4种批量插入方式及ID返回姿势 |
Java书签 #解锁MyBatis的4种批量插入方式及ID返回姿势
|
1. 今日书签
项目开发中，我们经常会用到单条插入和批量插入。但是实际情况可能是，项目初期由于种种原因，在业务各处直接使用单条插入SQL进行开发（未开启批处理），在后面的迭代中，系统性能问题渐渐凸显，然后再通过技术优化，大面积的对单条插入SQL、单条更新SQL进行批量插入、批量更新优化。这不可取，但确实存在。 那数据的批量 insert/update 有几种方式实现呢？ 哪些批量操作能直接获取到数据入库后的自增ID呢？ 它们的优点、缺点是分别是什么呢？ 在优化的过程中可能会出现哪些异常呢？我们要注意什么呢？… 这里我们列举4种数据批量保存方式，并对不同方式进行性能测试和对比分析。 2. 解锁方案 2.1. 前置任务 2.1.1. 标签 foreach 标签： myBatis-3-mapper.dtd 中 foreach 元素的属性主要有 item，index，collection，open，separator，close 这6种。 属性含义collection表示需要进行批量操作的对象集合item表示集合中每一个元素进行迭代时的别名index用于表示在迭代过程中，每次迭代到的位置open表示该语句以什么开始separator表示在每次进行迭代之间以什么符号作为分隔符close表示以什么结束trim 标签： 属性含义prefix在trim标签内SQL语句加上前缀suffix在trim标签内SQL语句加上后缀suffixOverrides去除trim标签内SQL语句多余的后缀内容prefixOverrides去除trim标签内SQL语句多余的前缀内容案例： MaBatis 生成批量插入SQL语句中：insert into t_item (id, product_id, product_name) values (1, ‘999’ , ), (2, ‘葡萄糖’ , ), … MaBatis 生成批量插入SQL语句后：insert into t_item (id, product_id, product_name) values (1, ‘999’), (2, ‘葡萄糖’) 2.1.2. 配置需要添加 JDBC 配置 allowMultiQueries=true，开启批处理。比如修改 mysql jdbc 的连接参数为： spring.datasource.druid.wei.url = jdbc:mysql://192.168.1.1:3306/wei?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&allowMultiQueries=true2.2. 四种数据批量插入方式 方式1：循环所有对象，生成固定字段的 VALUES int batchSaveItemLogDo1(@Param("list") List logList); INSERT INTO t_item_operate_log ( product_id, node_type, node_scene, node_data, `operator` ) VALUES ( #{item.productId,jdbcType=BIGINT}, #{item.nodeType,jdbcType=INTEGER}, #{item.nodeScene,jdbcType=INTEGER}, #{item.nodeData,jdbcType=VARCHAR}, #{item.operator,jdbcType=VARCHAR} ) 这种批量插入的SQL使用了 MyBatis 的 foreach 标签来实现批量插入功能。与下面的SQL相比，它使用了更加简洁的语法来构建批量插入的SQL语句。比较常用。 优点： 语法简洁：使用 foreach 标签可以使SQL语句更加简洁，直观地表示批量插入的值。性能优化：通过将多个插入值合并为一条INSERT语句，将所有数据一次性插入，可以减少与数据库的交互次数，从而提高性能。自动生成主键：通过设置 useGeneratedKeys="true" 和 keyProperty="list.id" ，可以在插入数据时自动生成主键，并且将自动生成的主键值回写到Java对象中（通过 list 对象可拿到全部数据入库后的自增ID）。缺点： 数据量限制：批量插入的数据量可能受到数据库配置和性能限制。对于非常大的数据量，需要考虑进一步优化或使用其他导入数据的方式。SQL注入风险：虽然这种方式使用了 MyBatis 的参数绑定，但仍需谨慎对待输入值，以防止SQL注入攻击。不支持条件判断：这种方式将所有数据一次性插入，不支持对某些字段进行条件判断是否插入。如果需要在插入时根据条件判断是否插入某些字段，可参考下面的批量插入方式。注意事项： 此方案 insert 的字段是固定的，不能动态适配此批量插入方案须要保证传给持久层 Dao 接口的参数 list 对象（需要批量插入的对象）中每个对象的属性字段个数与SQL中指定的 insert 字段个数一致，且属性名一致，顺序不能错异常案例： 如果 Dao 接口的参数 list 对象（需要批量插入的对象）中每个对象的属性字段个数与SQL中指定的 insert 字段个数不一致，或者，表中指定不允许为 NULL 的字段，在入参对象中其值为 NULL，则会出现异常： ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'node_scene' cannot be null ### The error may exist in file [D:\Ct_ iSpace\tan\wei-saas\saas-persistence\target\classes\com\meiwei\tan\saas\persistence\item\ItemOperateLogDao.xml] ### The error may involve com.meiwei.tan.saas.persistence.item.mapper.ItemOperateLogDao.batchSaveItemLogDo1-Inline ### The error occurred while setting parameters ### SQL: INSERT INTO t_item_operate_log ( product_id, node_type, node_scene, node_data, `operator` ) VALUES ( ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ? ) ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'node_scene' cannot be null ; Column 'node_scene' cannot be null; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'node_scene' cannot be null此异常原因是，我去掉了对象中 nodeScene 属性的设值，但插入SQL中需要插入 node_scene 该字段，而且表中该字段为非NULL无默认值。 方式2：循环所有对象，根据每个对象的不同字段值选择性生成 VALUES int batchSaveItemLogDo2(@Param("list") List logList); INSERT INTO t_item_operate_log ( product_id, node_type, node_scene, node_data, `operator` ) VALUES #{item.productId,jdbcType=BIGINT}, #{item.nodeType,jdbcType=INTEGER}, #{item.nodeScene,jdbcType=INTEGER}, #{item.nodeData,jdbcType=VARCHAR}, #{item.operator,jdbcType=VARCHAR}, 这种批量插入方式与上面的SQL非常相似，同样也是使用了 MyBatis 的 foreach 标签来实现批量插入功能。主要区别在于对空值的处理和SQL语句的拼接方式。比较常用。 优点： 空值处理：这种方式对空值的处理更加细致，在插入时能够将空字段插入为空字符串，避免插入NULL值。性能优化：通过直接将每个字段值拼接在一起，可以减少拼接SQL语句的时间和内存开销，可能略微提高性能。代码可读性：通过使用 trim标签 和 if条件，能够更清晰地看到插入值的拼接逻辑，SQL的拼接方式更灵活，使得代码更易读懂和维护。自动生成主键：通过设置 useGeneratedKeys="true" 和 keyProperty="list.id"，可以在插入数据时自动生成主键，并且将自动生成的主键值回写到Java对象中（通过 list 对象可拿到全部数据入库后的自增ID）。缺点： 长SQL语句：这种方式生成的SQL语句字段越多SQL越长，尤其当数据量较大时，多个插入值的拼接逻辑可能导致SQL语句较长。数据量限制：批量插入的数据量可能受到数据库配置和性能限制。对于非常大的数据量，需要考虑进一步优化或使用其他导入数据的方式。注意事项： 此方案 insert 的字段是固定的，不能动态适配备注： 此批量插入方式在 方式1 上有所优化，可以有效规避方案1中 Dao 接口的参数 list 对象（需要批量插入的对象）中每个对象的属性字段个数与SQL中指定的 insert 字段个数不一致，或者，表中指定不允许为 NULL 的字段，但在入参对象中其值为 NULL 的场景。 方式3：循环所有对象，为每个对象生成一个 INSERT 语句，一次执行多条SQL INSERT INTO t_item_operate_log ( product_id, node_type, node_scene, node_data, `operator` ) VALUES ( #{item.productId,jdbcType=BIGINT}, #{item.nodeType,jdbcType=INTEGER}, #{item.nodeScene,jdbcType=INTEGER}, #{item.nodeData,jdbcType=VARCHAR}, #{item.operator,jdbcType=VARCHAR} ) 这种批量插入的SQL使用了 MyBatis 框架的 动态SQL特性 和 foreach标签，将多条数据一次性插入到数据库中。 优点： 性能优化：使用批量插入可以减少与数据库的交互次数，从而提高性能。相较于逐条插入，批量插入能够减少网络开销和数据库连接/关闭的开销。代码简洁：通过使用MyBatis的foreach标签，可以在SQL中直接处理Java集合（java.util.List）并且将多条数据一次性插入到数据库中，使得代码更加简洁、易读。自动生成主键：通过设置 useGeneratedKeys="true" 和 keyProperty="list.id"，可以在插入数据时自动生成主键，并且将自动生成的主键值回写到Java对象中。可维护性：由于批量插入的SQL代码较为简洁，维护起来更加容易。缺点： 一次性插入的数据量限制：虽然批量插入可以减少与数据库的交互次数，但一次性插入的数据量可能受到数据库配置和性能限制。对于非常大的数据量，可能需要考虑进一步优化或使用其他导入数据的方式。数据库兼容性：不是所有的数据库都对批量插入提供了良好的支持。不同的数据库可能对批量插入的语法有所不同，这需要根据实际情况进行适配。SQL注入风险：使用MyBatis的foreach标签时，要确保在生成SQL语句时，正确处理输入值，以防止SQL注入攻击。注意事项： 此方案 insert 的字段是固定的，不能动态适配此方案批量插入后，只能获取到第1个对象的自增ID，拿不到全部数据入库后的自增ID此方案需要对 DruidDataSource 的 WallConfig 属性 setMultiStatementAllow 设置为 true（见下），不然可能会出现异常：Error updating database. Cause: java.sql.SQLException: sql injection violation, multi-statement not allow : insert into t_table备注： 此方案比较少见。基本思路是组装好所有需要批量插入的对象后一次执行多条SQL。但这里的看似一次提交，实际是一条一条提交，所以效率比较慢。 @Configuration public class MyBatisConfiguration { @Configuration @MapperScan(basePackages = {"com.meiwei.tan.saas.persistence.*"}, sqlSessionFactoryRef = "sqlSessionFactory4Wei") protected static class MyBatisDataSourceConfiguration4Wei { @Bean @Primary @ConfigurationProperties("spring.datasource.druid.wei") public DataSource dataSource4Wei() { DruidDataSource druidDataSource = new DruidDataSource(); List filterList = new ArrayList(); filterList.add(wallFilter()); filterList.add(statFilter()); druidDataSource.setProxyFilters(filterList); return druidDataSource; } public WallFilter wallFilter() { WallFilter wallFilter = new WallFilter(); wallFilter.setConfig(wallConfig()); return wallFilter; } public StatFilter statFilter() { return new StatFilter(); } public WallConfig wallConfig() { WallConfig wallConfig = new WallConfig(); wallConfig.setMultiStatementAllow(true); return wallConfig; } } // ...... }方式4：循环所有对象，根据 Mapper 接口或 Dao 接口传入的字段参数，动态可选生成 VALUES INSERT INTO t_item_operate_log ( ${column.escapedColumnName} ) VALUES ( #{item.productId,jdbcType=BIGINT} #{item.nodeType,jdbcType=INTEGER} #{item.nodeScene,jdbcType=INTEGER} #{item.nodeData,jdbcType=VARCHAR} '' #{item.operator,jdbcType=VARCHAR} '' ) 这种批量插入方式与之前给出的SQL有相似之处，也是使用了 MyBatis 的 foreach标签 来实现批量插入功能。主要区别在于对字段列的选择和对空值的处理。 区别： 对列的选择：在这种方式中，通过使用 标签 和 selective参数，可以动态选择要插入的列。${column.escapedColumnName} 会根据传入的 selective参数，动态生成要插入的列名。对空值的处理：对于插入的每个字段，通过 条件判断，对空值进行了特殊处理。如果字段值为空，则插入为空字符串’'，避免插入NULL值。优势： 列的选择：通过selective参数，可以动态选择要插入的列，使得插入操作更灵活和可配置。对空值的处理：与之前方式相似，对空值的处理更为细致，将空字段插入为空字符串，避免插入NULL值。性能优化：通过将多个插入值合并为一条INSERT语句，可以减少与数据库的交互次数，从而提高性能。自动生成主键：通过设置 useGeneratedKeys="true" 和 keyColumn="id" keyProperty="list.id"，可以在插入数据时自动生成主键，并且将自动生成的主键值回写到Java对象中（通过 list 对象可拿到全部数据入库后的自增ID）。劣势： 数据量限制：批量插入的数据量可能受到数据库配置和性能限制。对于非常大的数据量，可能需要考虑进一步优化或使用其他导入数据的方式。SQL注入风险：虽然这种方式使用了MyBatis的参数绑定，但仍需谨慎对待输入值，以防止SQL注入攻击。注意事项： 与其它方案不同的是，此方案中 foreach 的每个 if SQL注入中，不要习惯性加逗号（,）哦，加了会报错此方案需要对插入对象进行字段枚举配置，支持 Dao 接口接收需要插入的可选入参字段，见下文备注： 该批量插入方案可解决其它方案中不能动态按入参字段进行动态组装生成SQL语句的问题，也可有效规避方案1中 Dao 接口的参数 list 对象（需要批量插入的对象）中每个对象的属性字段个数与SQL中指定的 insert 字段个数不一致问题，或者，表中指定不允许为 NULL 的字段，但在入参对象中其值为 NULL 的场景。 import lombok.Builder; import lombok.Data; import java.io.Serializable; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; @Data @Builder public class ItemOperateLogDO implements Serializable { private Long id; private Long productId; /** * 节点操作类型（1-新增；2-更新；3-删除） */ private Integer nodeType; private Integer nodeScene; private String nodeData; private String operator; private Date operatorTime; private static final long serialVersionUID = 1L; /** * This enum was generated by MyBatis Generator. * This enum corresponds to the database table t_operate_detail * * @mbg.generated Tue Mar 02 10:29:28 CST 2021 */ public enum Column { id("id", "id", "BIGINT", false), productId("product_id", "productId", "BIGINT", false), nodeType("node_type", "nodeType", "INTEGER", false), nodeScene("node_scene", "nodeScene", "INTEGER", false), nodeData("node_data", "nodeData", "VARCHAR", false), operator("operator", "operator", "VARCHAR", false); private static final String BEGINNING_DELIMITER = "\""; private static final String ENDING_DELIMITER = "\""; private final String column; private final boolean isColumnNameDelimited; private final String javaProperty; private final String jdbcType; public String value() { return this.column; } public String getValue() { return this.column; } public String getJavaProperty() { return this.javaProperty; } public String getJdbcType() { return this.jdbcType; } Column(String column, String javaProperty, String jdbcType, boolean isColumnNameDelimited) { this.column = column; this.javaProperty = javaProperty; this.jdbcType = jdbcType; this.isColumnNameDelimited = isColumnNameDelimited; } public String desc() { return this.getEscapedColumnName() + " DESC"; } public String asc() { return this.getEscapedColumnName() + " ASC"; } public static ItemOperateLogDO.Column[] excludes(ItemOperateLogDO.Column... excludes) { ArrayList columns = new ArrayList(Arrays.asList(ItemOperateLogDO.Column.values())); if (excludes != null && excludes.length > 0) { columns.removeAll(new ArrayList(Arrays.asList(excludes))); } return columns.toArray(new ItemOperateLogDO.Column[]{}); } public static ItemOperateLogDO.Column[] all() { return ItemOperateLogDO.Column.values(); } public String getEscapedColumnName() { if (this.isColumnNameDelimited) { return new StringBuilder().append(BEGINNING_DELIMITER).append(this.column).append(ENDING_DELIMITER).toString(); } else { return this.column; } } public String getAliasedEscapedColumnName() { return this.getEscapedColumnName(); } } }2.3. Dao接口（Mapper接口） 如上各个批量SQL对应的 Mapper 接口（Dao 接口）： public interface ItemOperateLogDao { int batchSaveItemLogDo0(ItemOperateLogDO record); int batchSaveItemLogDo1(@Param("list") List list); int batchSaveItemLogDo2(@Param("list") List list); int batchSaveItemLogDo3(@Param("list") List list); int batchSaveItemLogDo4Selective(@Param("list") List list, @Param("selective") ItemOperateLogDO.Column ... selective); }2.4. Service接口 public interface ItemOperateLogService { /** 循环所有对象，普通单条插入（未开启批处理） */ Map batchSaveItemLogDo0(List itemOperateLogDOList); /** 方案1：循环所有对象，生成固定字段的 VALUES */ Map batchSaveItemLogDo1(List itemOperateLogDOList); /** 方案2：循环所有对象，根据每个对象的不同字段值选择性生成 VALUES */ Map batchSaveItemLogDo2(List itemOperateLogDOList); /** 方案3：循环所有对象，为每个对象生成一个 INSERT 语句，一次执行多条SQL */ Map batchSaveItemLogDo3(List itemOperateLogDOList); /** 方案4：循环所有对象，根据 Mapper 接口或 Dao 接口传入的字段参数，动态可选生成 VALUES */ Map batchSaveItemLogDo4Selective(List itemOperateLogDOList); } 2.5. 测试结果 批量保存方法（batchSaveItemLogDo0）插入数据1000条，耗时15666毫秒，批量保存后获取自增ID抽样结果：{"第1个对象的自增ID":"1","中间对象的自增ID":"500","最后1个对象的自增ID":"1000"} 批量保存方法（batchSaveItemLogDo1）插入数据1000条，耗时360毫秒，批量保存后获取自增ID抽样结果：{"第1个对象的自增ID":"1001","中间对象的自增ID":"1500","最后1个对象的自增ID":"2000"} 批量保存方法（batchSaveItemLogDo2）插入数据1000条，耗时490毫秒，批量保存后获取自增ID抽样结果：{"第1个对象的自增ID":"2001","中间对象的自增ID":"2500","最后1个对象的自增ID":"3000"} 批量保存方法（batchSaveItemLogDo3）插入数据1000条，耗时10583毫秒，批量保存后获取自增ID抽样结果：{"第1个对象的自增ID":"3001","中间对象的自增ID":"null","最后1个对象的自增ID":"null"} 批量保存方法（batchSaveItemLogDo4）插入数据1000条，耗时365毫秒，批量保存后获取自增ID抽样结果：{"第1个对象的自增ID":"4001","中间对象的自增ID":"4500","最后1个对象的自增ID":"5000"} 2.6. 性能对比 1）横向性能对比分析 所以，使用时需要根据实际应用场景进行方案选择，以及注意其方案的优劣和注意事项。 2）纵向性能对比分析 但要注意的是，在 MySQL 5.7 及更早版本中，数据包大小默认设置是 4M。如果不注意实际场景，一味调大切片大小，很可能会造成 SQL 语句执行失败。 异常如：“Could not execute JDBC batch update”。 虽然可以通过修改 mysql 的配置文件（my.cnf 或 my.ini）中的 max_allowed_packet = 8M 来解决，但是大的SQL同样可能会异变成慢SQL，得不偿失。 注意： 修改 max_allowed_packet 的值时，应该根据实际需求和数据库负载进行合理的调整，避免设置过大导致资源浪费或设置过小无法满足插入或更新大数据量的需求。比如：批量插入比较简单的字段和信息时，切片大小可以调到500；但如果，批量记录的是复杂的信息，一个字段可能就是 3000 Varchar 时，那500大小的切片显然不合适。所以，实际中需要先评估数据量与字段多少，再看合适与否。 3. 共性异常 附带几种批量SQL中常见的异常： 3.1. 如果 SQL 中 foreach 标签下，collection="list" 配置的 list 与 Dao 接口入参对象使用的 @Param("list") 名称不一致，如 collection="productList"，则会出现异常： org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'lists' not found. Available parameters are [list, param1]
3.2. 如果 SQL 中 foreach 标签下，SQL注入时未使用 item. 规则循环每个对象，不管是一个属性未使用 item.，还是多个属性未使用 item.（此处 foreach 中的 item="item" 只是列举，item 也可以自定义名称），则会出现异常： org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'nodeType' not found. Available parameters are [list, param1]
3.3. 如果 SQL 中 foreach 标签下，编写的属性名称与接口入参对象中的属性名称不一致，则会出现异常： org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'nodeTypes' in 'class com.meiwei.tan.saas.persistence.item.model.ItemOperateLogDO'
3.4 这个错误是因为 SQL 语句被检测到可能存在 SQL 注入漏洞，因此不允许一次执行多个 SQL 语句。这通常发生在某些数据库驱动程序或连接池的配置中，它们可能会检测到并禁止多个 SQL 语句的执行，以防止 SQL 注入攻击。 org.springframework.jdbc.UncategorizedSQLException: ### Error updating database. Cause: java.sql.SQLException: sql injection violation, multi-statement not allow : insert into t_table或者，对 DruidDataSource 的 WallConfig 属性 setMultiStatementAllow 设置为 true 进行兼容一次多个 SQL 语句的执行。 4. 其它批量插入操作 4.1） 如果需要批量插入数据，并且希望在遇到重复键值或主键冲突时避免报错，可以使用 INSERT IGNORE 或 INSERT … ON DUPLICATE KEY UPDATE 这两种方法在插入时能够处理重复键的情况，前者忽略重复键，后者在遇到重复键时更新记录。 语法如下： 使用 INSERT IGNORE： INSERT IGNORE INTO your_table (column1, column2, column3) VALUES (value1, value2, value3), (value4, value5, value6), ...;使用 INSERT … ON DUPLICATE KEY UPDATE： INSERT INTO your_table (column1, column2, column3) VALUES (value1, value2, value3), (value4, value5, value6), ... ON DUPLICATE KEY UPDATE column1 = VALUES(column1), column2 = VALUES(column2), ...; INSERT IGNORE： 如果插入数据时发生了主键或唯一索引冲突，即要插入的记录已存在，那么数据库会忽略该条插入操作，而不会报错。如果插入的数据与已有记录冲突，该插入操作会被静默丢弃，不会触发错误，也不会返回自动生成的主键。INSERT … ON DUPLICATE KEY UPDATE： 如果插入数据时发生了主键或唯一索引冲突，即要插入的记录已存在，那么数据库会执行更新操作，而不是插入新记录。同时，该语句也可以用于返回自动生成的主键。4.2） 如果需要导入大量数据的情况，比如从CSV文件导入数据 LOAD DATA INFILE 语句可以通过读取本地文件将大量数据一次性导入数据库表中，这样可以实现高效的批量插入（仅适用于MySQL等数据库）。 注意： 在使用 LOAD DATA INFILE 语句时，数据直接从文件导入到数据库表中，绕过了MySQL的INSERT语句，因此不会触发INSERT操作，也就不会返回自动生成的主键。 语法如下： LOAD DATA INFILE 'file_path' INTO TABLE your_table FIELDS TERMINATED BY ',' -- 或其他分隔符 LINES TERMINATED BY '\n' -- 或其他行终止符 (column1, column2, column3);5.总结 无论使用哪种方法，都应根据具体的需求和数据量选择最适合的方式。对于大量数据的批量插入，使用 LOAD DATA INFILE 通常会比INSERT 语句更高效。而对于少量数据的批量插入，使用 INSERT 语句可能更简便。此外，如果遇到可能会导致主键冲突的情况，可以选择合适的方法来处理重复键值。 |
通过4种批量插入方案横向对比可以发现：
通过两种数据量切片批量插入效果来看：通过调整切片列表大小，在一定范围内，可以有效提高批量插入效率。如上案例，从200一批，调到500一批，数据量超过10000时，500一批的批量插入要比200一批的速度快。【本文地址】
今日新闻 |
推荐新闻 |